﻿
namespace Gola.VN.Scb.Web.Dal
{
    class QueryConstants
    {
        #region provider
        public class Provider
        {
            /// <summary>
            /// Params: ParamExpressionId, ParamName, ParamDisplayName, ParamAddress, ParamEmail, ParamPhoneNumber, ParamLogo
            /// </summary>
            public static string Insert
            {
                get
                {
                    return "Insert into Provider(ExpressionId, Name, DisplayName, Address, Email, PhoneNumber, Logo) " +
                        string.Format("values({0}, {1}, {2}, {3}, {4}, {5}, {6})",
                        ParamExpressionId, ParamName, ParamDisplayName, ParamAddress, ParamEmail, ParamPhoneNumber, ParamLogo);
                }
            }

            /// <summary>
            /// Params: ParamExpressionId, ParamName, ParamDisplayName, ParamAddress, ParamEmail, ParamPhoneNumber, ParamLogo
            /// </summary>
            public static string Update
            {
                get
                {
                    return string.Format("Update Provider set " +
                        "DisplayName = {0}, Address = {1}, Email = {2}, PhoneNumber = {3}, Logo = {4} " +
                        "Where Name={5}",
                        ParamDisplayName, ParamAddress, ParamEmail, ParamPhoneNumber, ParamLogo, ParamName);
                }
            }

            public const string ParamExpressionId = "@ExpressionId";
            public const string ParamName = "@Name";
            public const string ParamDisplayName = "@DisplayName";
            public const string ParamAddress = "@Address";
            public const string ParamEmail = "@Email";
            public const string ParamPhoneNumber = "@PhoneNumber";
            public const string ParamLogo = "@Logo";

            public const string GetAll = @"SELECT [ID], [NAME], [ADDRESS], DISPLAYNAME, LOGO, EMAIL, PHONENUMBER, EXPRESSIONID FROM [PROVIDER]";
        }
        #endregion

        #region category
        public class Category
        {
            public static string GetCategoriesAsTree
            {
                get
                {
                    return string.Format("select {0}, {1}, {2}, {3} from category order by cast({0} as nvarchar)",
                        Id, Name, Notes, ParentId);
                }
            }

            public static string GetAll = @"SELECT [ID], [NAME], [NOTES], [PARENTID] FROM [CATEGORY]";

            public const string Id = "ID";
            public const string Name = "NAME";
            public const string Notes = "NOTES";
            public const string ParentId = "PARENTID";
        }
        #endregion

        #region product
        public class Product
        {
            public static string Insert
            {
                get
                {
                    return string.Format("insert into Product({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}) values(@{0}, " +
                        "@{1}, @{2}, @{3}, @{4}, @{5}, @{6}, @{7}, @{8})",
                        ProviderId, CategoryId, Title, Description, Image, Price, Currency, Promotion, OldPrice);
                }
            }

            public static string Update
            {
                get
                {
                    //just update view count
                    return @"UPDATE PRODUCT SET VIEWCOUNT=@VIEWCOUNT
                                            WHERE ID = @ID ";
                }
            }

            public const string ProviderId = "ProviderId";
            public const string CategoryId = "CategoryId";
            public const string Title = "Title";
            public const string Description = "Description";
            public const string Image = "Image";
            public const string Price = "Price";
            public const string Currency = "Currency";
            public const string Promotion = "Promotion";
            public const string OldPrice = "OldPrice";

            public const string GetAll = @"SELECT [PRODUCT].[ID]
                                      ,[PRODUCT].[PROVIDERID]
                                      ,[PRODUCT].[CATEGORYID]
                                      ,[PRODUCT].[CRAWLLINKID]
                                      ,[PRODUCT].[TITLE]
                                      ,[PRODUCT].[DESCRIPTION]
                                      ,[PRODUCT].[IMAGE]
                                      ,[PRODUCT].[LINK]
                                      ,[PRODUCT].[PRICE]
                                      ,[PRODUCT].[OLDPRICE]
                                      ,[PRODUCT].[CURRENCY]
                                      ,[PRODUCT].[PROMOTION]
                                      ,[PRODUCT].[CREATEDDATE]
                                      ,[PRODUCT].[PRODUCTID]
                                      ,[PROVIDER].[NAME] AS PROVIDERNAME
                                      ,[PROVIDER].[ADDRESS] AS PROVIDERADDRESS
                                      ,[CATEGORY].[NAME] AS CATEGORYNAME
                                      ,[PRODUCT].[VIEWCOUNT]
                                  FROM [PRODUCT] 
                                  INNER JOIN [PROVIDER] ON PROVIDER.ID = PRODUCT.PROVIDERID
                                  INNER JOIN [CATEGORY] ON CATEGORY.ID = PRODUCT.CATEGORYID";
        }
        #endregion

        #region sysinfo
        public class SysInfo
        {
            public const string GetSysInfo = "SELECT TOP 1 DATASTATUS FROM SYSTEMINFO";
            public const string UpdateSysInfo = "UPDATE SYSTEMINFO SET DATASTATUS = 3";
        }
        #endregion

        #region location
        public class Location
        {
            public const string GetAll = @"SELECT  [LATITUDE], [LONGTITUDE], [ADDRESS], [TITLE], [PROVIDERID] FROM [DBO].[LOCATION]";
        }
        #endregion

        #region searchhistory
        public class SearchHistory
        {
            public const string GetAll = @"SELECT Text, Link, Count, LastDate FROM SearchHistory";

            public const string UpdateSearchHistories = @"IF(EXISTS(SELECT TOP 1 * FROM SEARCHHISTORY WHERE TEXT=@TEXT))
                              UPDATE SEARCHHISTORY SET COUNT = COUNT + 1 
                           ELSE
                              INSERT SEARCHHISTORY(TEXT, LINK, COUNT) VALUES(@TEXT, @LINK, 1)";
        }
        #endregion
    }
}
